package com.hsnn.medstgmini.base.dict.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.Constants;
import com.hsnn.medstgmini.base.dict.dao.DicAreaDao;
import com.hsnn.medstgmini.base.dict.model.DicArea;
import com.hsnn.medstgmini.base.dict.model.form.DicAreaForm;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.trading.pur.dao.PurConfigDao;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.Pagination;
/**
 * This class interacts with Hibernate session to save/delete and
 * retrieve User objects.
 */
@Repository("dicAreaDao")
public class DicAreaDaoImpl  implements DicAreaDao {
	private static final Logger logger = LoggerFactory.getLogger(DicAreaDaoImpl.class);
	
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
    @Autowired
	private HibernateSqlUtil dao;
    @Value("#{sysProp['system.province']}")
    private String sysProvince;
    @Autowired
    private PurConfigDao pucDao;
    @Value("#{sysProp['system.setpuDelNumConfig']}")
    private String setpuDelNumConfig;
	private Session getSession(){
		Session session = sessionFactory.getCurrentSession();
		return session;
	}

    /**
     *@category 根据地区编码获取地区信息
     *@author 邱磊
     *@date 2015年6月17日 下午9:41:57
     *@param areaid
     *@return
     */
    public DicArea getDicAreaById(String areaid){
    	DicArea data = (DicArea) getSession().get(DicArea.class, areaid);
    	return data;
    }
   
    @SuppressWarnings("unchecked")
	public Map<String, Object> getSqlDicAreaById(String areaid){
    	String sql = "select * from dic_area t where t.area_id=:areaId";
    	Map<String, Object> params = new HashMap<String, Object>();
    	params.put("areaId", areaid);
    	List<Map<String, Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(sql, params);
    	if (null != list && list.size() > 0){
    		Map<String, Object> map = list.get(0);
    		return map;
    	}
    	return null;
    }
    
    @Override
    public Object getAreaById(String areaid){
    	StringBuffer sql = new StringBuffer();
    	sql.append("select area_id,area_name from dic_area where area_id=:areaid ");
    	Query query=getSession().createSQLQuery(sql.toString());
    	query.setParameter("areaid", areaid);
    	Object data = query.uniqueResult();
    	return data;
    }
    
	/**
	 * @category 通过区域名称获取 区域代码
	 * @author 韩守松
	 * @date   2015年6月13日
	 * @param  @param dicArea
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicArea> getDicAreaByArfullName(DicArea dicArea) {
		try{
			String  arfullname = dicArea.getArfullname();
			StringBuffer hql = new StringBuffer();
			hql.append("from DicArea where ar_fullname = ?");
			Query query = this.getSession().createQuery(hql.toString());
			query.setParameter(0, arfullname);
			return query.list();
		}catch(Exception e){
			logger.error("根据区域名称获取数据异常-->", e);
			return new ArrayList<DicArea>();
		}
	}
	
	/**
	 *@category 根据父ID查询出所有的地区信息
	 *@author 邱磊
	 *@date 2015年6月19日 上午8:44:05
	 *@param fatherId 父ID
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public List<DicArea> getDicAreaListByFatherId(String fatherId){
		String hql = "from DicArea t where t.father.areaid=:fatherId";
		Map<String, String> params = new HashMap<String, String>();
		params.put("fatherId", fatherId);
		return (List<DicArea>) dao.getList(hql, params);
	}
	
	/**
	 *@category 根据条件查询地区字典列表
	 *@author wangbing
	 *@date 2015年6月17日 下午9:41:34
	 *@param page
	 *@return
	 */
	@Override
	public Pagination getdicAreaList(Pagination page){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.area_id areaId,a.area_name areaName,a.ar_shortname arShortname,a.ar_fullname arFullname, ")
		   .append("  b.area_id fatherAreaCode,b.area_name fatherAreaName,a.status status FROM dic_area a    ")
		   .append("  LEFT JOIN dic_area b  ON a.father_id=b.area_id WHERE a.father_id <> '0' AND a.father_id IS NOT NULL ");
		Map<String,String> params = new HashMap<String,String>();
		String code = page.getConditions().get("serchareacode");
		if(StringUtils.isNotBlank(code)){
			sql.append(" and (a.area_id = :code ) ");
			params.put("code", code.trim());
		}
		String fatherAreaCode = page.getConditions().get("fatherAreaCode");
		if(StringUtils.isNotBlank(fatherAreaCode)){
			sql.append(" and (b.area_id like :fatherAreaCode )  ");
			params.put("fatherAreaCode", AreaUtil.escapeAreaId(fatherAreaCode.trim()));
		}
		String status = page.getConditions().get("status");
		if(StringUtils.isNotBlank(status)){
			sql.append(" and a.status = :status ");
			params.put("status", status.trim());
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 停用启用（包含批量）
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public int changeStatus(DicArea dicArea) {
		String arPrefix = AreaUtil.getAreaPrefix(dicArea);
		if (arPrefix == null) {
			return 0;
		}
		
		// 取出所有被停用的地区的ID
		List<String> switchId = new ArrayList<String>();
		if (0 == dicArea.getStatus()) {
			StringBuffer getChild = new StringBuffer();
			getChild.append("select * from dic_area where area_id like :areaId and status = '1'");

			Query query = getSession().createSQLQuery(getChild.toString());
			query.setParameter("areaId", arPrefix + "%");
			query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			List<Map<String, Object>> areas = (List<Map<String, Object>>)query.list();

			for (int i = 0; i < areas.size(); ++i) {
				Map<String, Object> so = (Map<String, Object>)areas.get(i);
				switchId.add((String) so.get("area_id"));
			}
		}
		
		// 启用、停用地区
		StringBuffer sql = new StringBuffer();
		Map<String, Object> params = new HashMap<String, Object>();
		sql.append("update dic_area set status= :status, upd_datetime= SYSDATE(), upd_user= :updUser ");
		
		if (0 == dicArea.getStatus()){
			sql.append(" where status = '1' and area_id like :areaId ");
			params.put("areaId", arPrefix+"%");
		} else {
			sql.append(" where area_id= :areaId ");
			params.put("areaId", dicArea.getAreaid());
			// 一次只启用一个地区
			switchId.add(dicArea.getAreaid());
		}
		params.put("status", dicArea.getStatus());
		params.put("updUser", dicArea.getUpdateuserid());
		dao.executeSql(sql.toString(), params);
		
		// 编辑日志信息
		StringBuffer logMsg = new StringBuffer();
		logMsg.append("状态:");
		if (0 == dicArea.getStatus()) {
			logMsg.append("启用,停用;");
		} else {
			logMsg.append("停用,启用;");
		}
		
		// 写入日志
		for (int i = 0; i < switchId.size(); ++i) {
			writeLog(switchId.get(i), logMsg.toString());
		}
		
		return switchId.size();
	}

	// 将变更的信息写入日志记录
	public void writeLog(String areaId, String detail) {
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("insert into dic_area_log(log_id,area_id,area_name,father_id,  ")
		   .append("ar_shortname,ar_fullname,status,upd_datetime,upd_user,sort_id, max_delnum_county, max_delnum_base,detail) select ")
		   .append(" UUID(),t.*,:detail from dic_area t where t.area_id= :areaId ");
		
		Query query=session.createSQLQuery(sql.toString());
		query.setString("areaId", areaId);
		query.setString("detail", detail);
		query.executeUpdate();
		session.flush();
	}
	
	/**
	 *@category 编辑 保存地区信息
	 *@author 邱磊
	 *@date 2015年6月10日 下午4:07:08
	 *@return
	 */
	@Override
	public void updateDicArea(Pagination page) {
		String updUser = page.getConditions().get("updUser");		
		String areaId = page.getConditions().get("areaId");
		String areaName = page.getConditions().get("areaName");
		String arFullName = page.getConditions().get("arFullName");
		String arShortName = page.getConditions().get("arShortName");
		String [] inValue = new String[] {areaId, areaName, arFullName, arShortName};
		String logStr = genLogStr(page, inValue);
		
		if (StringUtils.isNotBlank(logStr)) {
			StringBuffer sql = new StringBuffer();
			sql.append(" UPDATE dic_area SET area_name = :areaName, ")
			   .append("ar_shortname =:arShortName, ar_fullname =:arFullName,")
			   .append(" upd_datetime=:updDatetime, upd_user=:updUser WHERE area_id = :areaId ");
			
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("areaId", areaId);
			params.put("areaName",areaName );
			params.put("arFullName",arFullName );
			params.put("arShortName",arShortName );
			params.put("updUser",updUser );
			params.put("updDatetime", new Date());
			dao.executeSql(sql.toString(), params);
			
			String logsql = "insert into dic_area_log select UUID(),a.area_id,a.area_name,a.father_id,a.ar_shortname,"
					+ "a.ar_fullname,a.status,:detail,a.upd_datetime,a.upd_user,a.sort_id from dic_area a where a.area_id=:areaId";
			params.put("detail", logStr);
			params.remove("areaName");
			params.remove("arFullName");
			params.remove("arShortName");
			params.remove("updUser");
			params.remove("updDatetime");
			dao.executeSql(logsql, params);
		}
	}
	
	/**
	 *@category 新增 保存地区信息
	 *@author 邱磊
	 *@date 2015年6月10日 下午4:07:08
	 *@return
	 */
	@Override
	public void addDicArea(Pagination page) {
		String areaName = page.getConditions().get("areaName");
		String arFullName = page.getConditions().get("arFullName");
		String arShortName = page.getConditions().get("arShortName");
		String areaId = page.getConditions().get("areaId");
		String father = page.getConditions().get("father");
		String updUser = page.getConditions().get("updUser");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into dic_area(area_id,area_name,father_id,ar_shortname,ar_fullname,status,upd_datetime,upd_user,sort_id) values (")
		   .append(" :areaId,:areaName,:father,:arShortName,:arFullName,1,:updDatetime,:updUser,0) ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("areaName", areaName );
		params.put("arFullName", arFullName );
		params.put("arShortName", arShortName );
		params.put("updUser", updUser );
		params.put("updDatetime", new Date());
		params.put("areaId", areaId);
		params.put("father", father);
		dao.executeSql(sql.toString(), params);

		// 组合日志信息
		String [] inValue = null;
		String fatherName = "";
		if (StringUtils.isNotBlank(father)) {
			String schFather = "select a.* from dic_area a where a.area_id = :areaId";
			params = new HashMap<String, Object>();
			params.put("areaId", father);
			@SuppressWarnings("unchecked")
			List<Map<String, Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(schFather, params);
			if (null != list && list.size() > 0) {
				Map<String, Object> map = list.get(0);
				fatherName = (String)map.get("area_name");
			}
			inValue = new String[] {areaId, areaName, arFullName, arShortName, fatherName};
		} else {
			inValue = new String[] {areaId, areaName, arFullName, arShortName};
		}
		
		String logStr = genLogStr(inValue);
		writeLog(areaId, logStr);
	}
	
	/**
	 *@category 根据ID或name查询有无重复数据
	 *@author 邱磊
	 *@date 2015年6月10日 下午4:07:08
	 *@param data 新增的地区信息
	 *@return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicArea> getDicAreaBy(String key, String value, String areaId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from dic_area where ")
		   .append(key)
		   .append(" = ? and  area_id <> ? ");
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, value);
		query.setParameter(1,areaId);
		return query.list();
	}
	
	private static String [] title = new String[] {"地区编码", "地区名称", "地区完整名称", "地区简称", "上级地区名称", "上级地区编码", "状态"};
	private static String [] dbTitle = new String[] {"area_id", "area_name", "ar_fullname", "ar_shortname", "father_name", "father_id", "status"};

	private String genLogStr(String[] inValue) {
		StringBuffer logStr = new StringBuffer();

		if (inValue != null) {
		    for (int i = 0; i < inValue.length; ++i) {
		    	if (StringUtils.isNotBlank(inValue[i]))
		    	    logStr.append(title[i]).append(":,").append(inValue[i]).append(";");
		    } 
		}
		
		return logStr.toString();
	}
	
	@SuppressWarnings("unchecked")
	private String genLogStr(Pagination page, String[] inValue) {
		String areaId = page.getConditions().get("areaId");
		
		String sql = "select a.*,b.area_name as father_name from dic_area a left join dic_area b on a.father_id=b.area_id where a.area_id = :areaId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("areaId", areaId);
		List<Map<String, Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(sql, params);
		
		StringBuffer logStr = new StringBuffer();
		if (null != list && list.size() > 0) {
			Map<String, Object> map = list.get(0);
			for (int i= 0; i < inValue.length; ++i) {
				String value = (String)map.get(dbTitle[i]);
				
				if (StringUtils.isNotBlank(inValue[i]) && !value.equals(inValue[i])) {
					logStr.append(title[i]).append(":").append(value).append(",").append(inValue[i]).append(";");
				}
			}
		}
		return logStr.toString();
	}
	
	//将变更的信息写入日志记录
	@Override
	public void writeLog(DicArea dicArea, String msg){
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("insert into dic_area_log(log_id,area_id,area_name,father_id,  ")
		   .append("ar_shortname,ar_fullname,status,upd_datetime,upd_user,sort_id, max_delnum_county, max_delnum_base,detail) select ")
		   .append(" UUID(),t.*,:msg from dic_area t where t.area_id= :areaId ");
		
		if ("0".equals(dicArea.getStatus()+"")) {
			sql.append(" or t.father_id= :areaId or t.father_id in (select a.area_id from dic_area a where a.father_id = :areaId )");
		}
		Query query = session.createSQLQuery(sql.toString());
		query.setString("areaId",dicArea.getAreaid());
		query.setString("msg", msg);
		query.executeUpdate();
		session.flush();
	}
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getAreaInfoByName(String province, String areaName){
		Session session = sessionFactory.openSession();
		String sql = "select * from dic_area where area_name = :areaName and area_id like :province";
		Query query = session.createSQLQuery(sql);
		query.setString("areaName", areaName);
		query.setString("province", province + "%");
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.list();
		session.clear();
		session.close();
		return list;
	}
	/**
	 * 
	 *@category 查询本省所有的县区
	 *@author 邱磊
	 *@date 2015年11月19日 上午10:40:01
	 *@param 
	 *@return Pagination
	 */
	public Pagination getProvinceCounty(Pagination page){
		String orgArea = page.getConditions().get("orgArea");
		StringBuffer sql = new StringBuffer()
		.append("select * from dic_area t ")
		.append(" where t.status = 1")
		.append(" and t.area_id like :orgArea");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		// 按市设置
		if (Constants.CITY_DEL_NUM.equals(setpuDelNumConfig)){
			sql.append(AreaUtil.getAreaPrefix(orgArea).length()==6?"":" and t.area_id like '____00' and t.area_id not like '__0000'");
		}
		// 按区县
		else if (Constants.COUNTY_DEL_NUM.equals(setpuDelNumConfig)){
			sql.append("  and t.area_id not like '____00'");
		}
		params.put("orgArea", AreaUtil.getAreaPrefix(orgArea)+"%");
		if (StringUtils.isNotBlank(map.get("areaCode"))){
			sql.append(" and t.area_id = :areaCode");
			params.put("areaCode", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(map.get("areaName"))){
			sql.append(" and t.areaName = :areaName");
			params.put("areaName", map.get("areaName"));
		}
		if (StringUtils.isNotBlank(map.get("fatherCode"))){
			sql.append(" and t.father_id = :fatherCode");
			params.put("fatherCode", map.get("fatherCode"));
		}
		sql.append(" ORDER BY t.area_id");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	public void updateAreaDelCount(DicAreaForm form){
		String sql = "update dic_area set max_delnum_county = :maxDelnumCounty, max_delnum_base = :maxDelnumBase where area_id = :areaId";
		Map<String, Object> params =  new HashMap<String, Object>();
		params.put("maxDelnumCounty", form.getMaxDelnumCounty());
		params.put("maxDelnumBase", form.getMaxDelnumBase());
		params.put("areaId", form.getAreaId());
		dao.executeSql(sql, params);
		newWriteLog(form.getAreaId());
	}
	private void newWriteLog(String areaId){
		StringBuffer sql = new StringBuffer()
		.append("insert into dic_area_log(log_id,area_id,area_name,father_id,ar_shortname,ar_fullname,")
		.append(" status,detail,upd_datetime,upd_user,sort_id,max_delnum_county,max_delnum_base)")
		.append(" select UUID(),t.area_id,t.area_name,t.father_id,t.ar_shortname,t.ar_fullname,")
		.append(" t.status,null,t.upd_datetime,t.upd_user,t.sort_id,t.max_delnum_county,t.max_delnum_base ")
		.append(" from dic_area t where t.area_id= :areaId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("areaId", areaId);
		dao.executeSql(sql.toString(), params);
	}
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getAreaByFatherId(String fatherId){
		String sql = "select * from dic_area where father_id = :fatherId and status=1";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("fatherId", fatherId);
		return (List<Map<String, Object>>) dao.getSqlMapList(sql, params);
	}
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getEveyAreaDelNum(){
		String sql = "select MIN(max_delnum_base) max_delnum_base,min(max_delnum_county) max_delnum_county from dic_area t where t.area_id like :subProvince  and t.area_id not like '____00'";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("subProvince", sysProvince.substring(0,2) + "%");
		return  (List<Map<String, Object>>) dao.getSqlMapList(sql, params);
	}
	public void setupEveryAreaDel(Pagination page){
		String sql = "update dic_area set max_delnum_county = :maxDelnumCounty, max_delnum_base = :maxDelnumBase where area_id like :subprovince  and area_id not like '____00'";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("subprovince", sysProvince.subSequence(0, 2) + "%");
		params.put("maxDelnumCounty", page.getConditions().get("countyDelNum"));
		params.put("maxDelnumBase", page.getConditions().get("baseDelNum"));
		dao.executeSql(sql, params);
		StringBuffer logsql = new StringBuffer()
		.append("insert into dic_area_log(log_id,area_id,area_name,father_id,ar_shortname,ar_fullname,")
		.append(" status,detail,upd_datetime,upd_user,sort_id,max_delnum_county,max_delnum_base)")
		.append(" select UUID(),t.area_id,t.area_name,t.father_id,t.ar_shortname,t.ar_fullname,")
		.append(" t.status,null,t.upd_datetime,t.upd_user,t.sort_id,t.max_delnum_county,t.max_delnum_base ")
		.append(" from dic_area t")
		.append(" where t.area_id like :subprovince  and t.area_id not like '____00'");
		params.remove("maxDelnumCounty");
		params.remove("maxDelnumBase");
		dao.executeSql(logsql.toString(), params);
	}
	@SuppressWarnings("unchecked")
	public Map<String, Object> getAreaByAreaId(String areaId){
		String sql = "select * from dic_area t where t.area_id = :areaId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("areaId", areaId);
		List<Map<String, Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(sql, params);
		if (null != list && list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getMyProvinceArea(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select * from dic_area t")
		.append(" where t.area_id like :subprovince")
		.append(" and t.area_id not like '____00' and t.status=1");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("subprovince", sysProvince.substring(0, 2) +"%");
		if (StringUtils.isNotBlank(map.get("areaCode"))){
			sql.append(" and t.area_id = :areaCode");
			params.put("areaCode", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(map.get("areaName"))){
			sql.append(" and t.area_name like :areaName");
			params.put("areaName", "%" + map.get("areaName") + "%");
		}
		if (StringUtils.isNotBlank(map.get("fatherId"))){
			sql.append(" and t.father_id = :fatherId");
			params.put("fatherId", map.get("fatherId"));
		}
		return (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(), params);
	}
	/**
	 *@category 获取未申报过的地区列表(配送企业)
	 *@author 应晓川
	 *@date 2015年11月20日14:50:30
	 *@param 
	 *@return Pagination
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getMyProvinceAreaByPs(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select t.* from dic_area t left join base_del_area_rel b on t.area_id=b.area_code and b.del_comp_code=:compCode ")
		.append(" where b.area_code is null and t.area_id like :subprovince")
		.append(" and t.area_id not like '____00' and t.status=1");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("subprovince", sysProvince.substring(0, 2) +"%");
		if (StringUtils.isNotBlank(map.get("compCode"))){
			params.put("compCode", map.get("compCode"));
		}
		if (StringUtils.isNotBlank(map.get("areaName"))){
			sql.append(" and t.area_name like :areaName");
			params.put("areaName", "%" + map.get("areaName") + "%");
		}
		if (StringUtils.isNotBlank(map.get("fatherId"))){
			sql.append(" and t.father_id = :fatherId");
			params.put("fatherId", map.get("fatherId"));
		}
		return (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(), params);
	}
}